#include "databaseinterface.h"

DatabaseInterface::DatabaseInterface(QObject *parent) :
    QObject(parent)
{

    db = QSqlDatabase::addDatabase("QMYSQL", QUuid::createUuid().toString());
    //db.setConnectOptions();
    db.setHostName("localhost");
    db.setDatabaseName("mydb");
    db.setUserName("root");
    db.setPassword("rkghkonly");
    if(db.open())
    {
        qDebug() << "db opened!";
    }
    else
    {
        qDebug() << "DB Error: " << db.lastError().text();
    }
}

DatabaseInterface::~DatabaseInterface()
{
    db.close();
    qDebug() << "db closed!";
}

void DatabaseInterface::attemptLogIn(const QString &username, const QString &hashed_pw)
{
    QString qryString = "SELECT privilege FROM user WHERE username = \'" + username + "\' and hashed_pw = \'" + hashed_pw + "\';";

    QSqlQuery loginAttempt_qry(qryString, db);

    qDebug() << "Logging in..";
    if(loginAttempt_qry.exec(qryString)) // Make a query
    {

        if (loginAttempt_qry.next())
        {
            emit logInVerified(true, loginAttempt_qry.value(0).toInt());
            qDebug() << loginAttempt_qry.value(0).toString();
        }
        else
        {
            qDebug() << "doesn't exist";
            emit logInVerified(false, -1);
        }

    }
    else
    {
        qDebug() << "query request failed";
        emit logInVerified(false, -1);
    }

}

/**
 *  Assume userList contain only one user(QString) for now...
 */
void DatabaseInterface::retrieveLogs(QStringList *userList)
{
    foreach(QString username, *userList)
    {
        qDebug() << "username: " + username;

        QString qryString = "SELECT owner,log,timestamp FROM log WHERE owner = \'" + username + "\';";

        QSqlQuery retrieveLog_qry(qryString, db);

        if(retrieveLog_qry.exec(qryString)) // Make a query
        {

            while (retrieveLog_qry.next())
            {
                qDebug() << retrieveLog_qry.value(0).toString();
                qDebug() << retrieveLog_qry.value(1).toString();
                qDebug() << retrieveLog_qry.value(2).toString().replace("T"," ",Qt::CaseSensitive);
                emit sendLog(retrieveLog_qry.value(0).toString(), retrieveLog_qry.value(1).toString(),
                             retrieveLog_qry.value(2).toString().replace("T"," ",Qt::CaseSensitive));
                //emit logInVerified(true, retrieveLog_qry.value(0).Int);
                //qDebug() << retrieveLog_qry.value(0).toString();

            }
            emit queryDone();

        }
        else
        {
            qDebug() << "query failed";

        }
    }
    delete userList;
}

void DatabaseInterface::addNewLog(const QString &username, const QString &log)
{
    QString currentTime = QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");

    QString qryString = "insert into log (log_id,owner,log,timestamp) values (0,\'" +
            username + "\',\'" + log + "\',\'" + currentTime + "\');";

    QSqlQuery newLog_qry(qryString, db);


    if(newLog_qry.prepare(qryString)) // Make a query
    {
        //Success
        emit newLogSuccess(currentTime);

        /*
        if (newLog_qry.next())
        {
            emit logInVerified(true, newLog_qry.value(0).toInt());
            qDebug() << newLog_qry.value(0).toString();
        }
        else
        {
            qDebug() << "doesn't exist";
            //emit logInVerified(false, -1);
        }*/
    }
    else
    {
        qDebug() << "query failed";
        //emit logInVerified(false, -1);
        emit newLogFailed();
    }
}

void DatabaseInterface::getAccessList(const QString &owner)
{
    QString qryString2 = "SELECT privilege FROM user WHERE username = \'" + owner + "\'";

    int privilegeCheck = -1;
    QSqlQuery retrieveAccessList_qry2(qryString2, db);

    if(retrieveAccessList_qry2.exec(qryString2)) // Make a query
    {
        //QStringList *accessList = new QStringList;

        if (retrieveAccessList_qry2.next())
        {

            privilegeCheck = retrieveAccessList_qry2.value(0).toString().toInt();
        }


    }
    else
    {
        qDebug() << "query failed";
        emit accessListFailed("Query failed");

    }


    if(privilegeCheck == 0){

        QString qryString = "SELECT accessUser FROM access WHERE username = \'" + owner + "\'";


        QSqlQuery retrieveAccessList_qry(qryString, db);

        if(retrieveAccessList_qry.exec(qryString)) // Make a query
        {
            QStringList *accessList = new QStringList;

            while (retrieveAccessList_qry.next())
            {
                qDebug() << retrieveAccessList_qry.value(0).toString();
                accessList->append(retrieveAccessList_qry.value(0).toString());
            }
            //emit accessListSuccess(accessList);
            emit accessListSuccess(accessList);

        }
        else
        {
            qDebug() << "query failed";
            emit accessListFailed("Query failed");

        }
    }
    else if (privilegeCheck == 1){
        QString qryString = "SELECT username FROM user";


        QSqlQuery retrieveAccessList_qry(qryString, db);

        if(retrieveAccessList_qry.exec(qryString)) // Make a query
        {
            QStringList *accessList = new QStringList;

            while (retrieveAccessList_qry.next())
            {
                qDebug() << retrieveAccessList_qry.value(0).toString();
                accessList->append(retrieveAccessList_qry.value(0).toString());
            }
            //emit accessListSuccess(accessList);
            emit accessListSuccess(accessList);

        }
        else
        {
            qDebug() << "query failed";
            emit accessListFailed("Query failed");

        }
    }

}

void DatabaseInterface::addNewUser(const QString &newUser, const QString &pw, int privilege)
{
    qDebug() << "starting add new user query";
    // check if username exists already
    QString qryS = "SELECT username FROM user WHERE username=\'" + newUser + "\'";
    QSqlQuery qry(qryS, db);


    if(qry.exec(qryS)) // Make a query
    {
        if (qry.next())
        {
            qDebug() << "duplication username exists";
            emit newUserFailed("Duplicate username exists.");
        }
        else
        {
            qDebug() << "creating new user";
            QString qryString = "INSERT INTO user (username, hashed_pw, privilege) VALUES (\'" + newUser + "\','" + pw + "\',\'" + QString::number(privilege) + "\')";

            QSqlQuery retrieveAccessList_qry(qryString, db);
            retrieveAccessList_qry.exec(qryString);
            if(qry.exec(qryS) && qry.first()) // Make a query
            {
                //QStringList *accessList = new QStringList;

                //while (retrieveAccessList_qry.next())
                //{
                //    accessList->append(retrieveAccessList_qry.value(0).toString());
                //}
                //emit accessListSuccess(accessList);
                emit newUserSuccess(newUser);
            }
            else
            {
                qDebug() << "query failed";
                emit newUserFailed("Query failed");
            }
        }
    }
}

void DatabaseInterface::removeUser(const QString &userToBeDeleted)
{
    QString qryString = "DELETE FROM user WHERE username=\'" + userToBeDeleted + "\'";

    QSqlQuery retrieveAccessList_qry(qryString, db);

    if(retrieveAccessList_qry.exec(qryString) && !retrieveAccessList_qry.first()) // Make a query
    {
        //QStringList *accessList = new QStringList;

        //while (retrieveAccessList_qry.next())
        //{
        //    accessList->append(retrieveAccessList_qry.value(0).toString());
        //}
        //emit accessListSuccess(accessList);
        emit removeUserSuccess();

    }
    else if (retrieveAccessList_qry.first()) {
        emit removeUserFailed("Database could not remove user");
    }
    else
    {
        qDebug() << "query failed";
        emit removeUserFailed("Query failed");

    }

}

void DatabaseInterface::addNewAccessRight(const QString &user, const QString &newAccessRight)
{
    qDebug() << "start addNewAccess Right to database";
    //check if manager.. if manager return nothing.. no mangerial access right exist on access table..
    QString qryS2 = "select privilege,username from user where username=\'" + user + "\'";
    QSqlQuery qry2(db);

    if (qry2.exec(qryS2))
    {
        if(qry2.next() && qry2.value(0) == 1){

            emit addNewAccessFailed("Manager can't add new accessRight");

        }
        else
        {
            //check if user is trying to add manger as accessRight..
            QString qryS3 = "select privilege from user where username=\'" + newAccessRight + "\'";
            QSqlQuery qry3(db);
            if (qry3.exec(qryS3))
            {
                if(qry3.next() && qry3.value(0) == 1)
                {
                    emit addNewAccessFailed("The user can't have access to a manager");

                } else
                {


                    // check if username exists already
                    QString qryS = "SELECT accessUser FROM access WHERE username=\'" + user + "\' AND accessUser=\'" + newAccessRight + "\'";
                    QSqlQuery qry(db);


                    if(qry.exec(qryS)) // Make a query
                    {
                        //qry.clear();

                        if (qry.next())
                        {

                            qDebug() << "duplicate accessRightuser exist";
                            emit addNewAccessFailed("Duplicate username exists.");
                        }
                        else
                        {
                            qDebug() << "what's wrong:" + qry.value(0).toString();
                            qDebug() << "addingNewaccessright sucess";
                            QString qryString = "INSERT INTO access (access_id, username, accessUser) VALUES (0,\'" + user + "\', \'" + newAccessRight + "\')";

                            QSqlQuery retrieveAccessList_qry(qryString, db);

                            if(retrieveAccessList_qry.prepare(qryString)) // Make a query
                            {
                                //QStringList *accessList = new QStringList;

                                //while (retrieveAccessList_qry.next())
                                //{
                                //    accessList->append(retrieveAccessList_qry.value(0).toString());
                                //}
                                //emit accessListSuccess(accessList);
                                emit addNewAccessSuccess();

                            }
                            else
                            {
                                qDebug() << "query failed";
                                emit addNewAccessFailed("Query failed");

                            }
                        }
                    }
                    else
            {
                emit addNewAccessFailed("Query failed");
            }


                }
            }
            else
            {
                emit addNewAccessFailed("Query Failed");
            }
        }
    }
    else
    {
        emit addNewAccessFailed("Query Failed");
    }
}

void DatabaseInterface::removeAccessRight(const QString &user, const QString &accessRightToBeDeleted)
{
    qDebug() << "start removing access Right";
    QString qryS = "select privilege from user where username=\'" + user + "\'";
    QSqlQuery qry(db);

    if (qry.exec(qryS))
    {
        if(qry.next() && qry.value(0) == 1){

            emit removeAccessRightFailed("Manager can't remove new accessRight");

        }
        else
        {

            QString qryString = "DELETE FROM access WHERE username=\'" + user + "\' AND accessUser=\'" + accessRightToBeDeleted + "\'";

            QSqlQuery retrieveAccessList_qry(db);

            if(retrieveAccessList_qry.exec(qryString)) // Make a query
            {
                //QStringList *accessList = new QStringList;

                //while (retrieveAccessList_qry.next())
                //{
                //    accessList->append(retrieveAccessList_qry.value(0).toString());
                //}
                //emit accessListSuccess(accessList);
                emit removeAccessRightSuccess();

            }
            else
            {
                qDebug() << "query failed";
                emit removeAccessRightFailed("Query failed");
            }
        }

    }
    else
    {
        emit removeAccessRightFailed("Query failed");
    }
}


